set hive.exec.dynamic.partition = true;
set hive.exec.dynamic.partition.mode = 'nonstrict';
set hive.exec.max.dynamic.partitions.pernode = 200;
set hive.exec.max.dynamic.partitions = 200;
with details as (
    select * from (
                      select
                          *
                           ,row_number() over(partition by network_code,cnt_date order by create_time) as rk
                      from
                          (
                              select
                                  network_code
                                   ,network_name
                                   ,create_time
                                   ,data_type
                                   ,province
                                   ,province_id
                                   ,abnormal_reason_code
                                   ,abnormal_reason
                                   ,if(effect_time_type=0 ,null,effect_time_start) as effect_time_start
                                   ,if(effect_time_type=0 ,null,effect_time_end) as effect_time_end
                                   ,'{{ execution_date | cst_ds }}' as cnt_date
                                   ,belong_proxy
                                   ,belong_proxy_code
                                   ,effect_time_type  -- 生效类型 1无线1有限
                              from jms_dim.dim_yl_oms_order_interceptor_base_dt
                              where  data_type in ('network','the_third_segment_code')
                                and is_enable=1
                                and is_delete=0
                                and dt='{{ execution_date | cst_ds }}'
                                and
                                  ((effect_time_type=1
                                      and ( date(effect_time_start)<='{{ execution_date | cst_ds }}' and date(effect_time_end)>='{{ execution_date | cst_ds }}'))
                                      or effect_time_type=0  )
                              union ALL

                              select
                                  network_code
                                   ,network_name
                                   ,create_time
                                   ,data_type
                                   ,province
                                   ,province_id
                                   ,abnormal_reason_code
                                   ,abnormal_reason
                                   ,if(effect_time_type=0 ,null,effect_time_start) as effect_time_start
                                   ,if(effect_time_type=0 ,null,effect_time_end) as effect_time_end
                                   ,date_add('{{ execution_date | cst_ds }}',-1) as cnt_date
                                   ,belong_proxy
                                   ,belong_proxy_code
                                   ,effect_time_type  -- 生效类型 1无线1有限
                              from jms_dim.dim_yl_oms_order_interceptor_base_dt
                              where data_type in ('network','the_third_segment_code')
                                and is_enable=1
                                and is_delete=0
                                and dt=date_add('{{ execution_date | cst_ds }}',-1)
                                and
                                  ((effect_time_type=1 and ( date(effect_time_start)<=date_add('{{ execution_date | cst_ds }}',-1) and date(effect_time_end)>=date_add('{{ execution_date | cst_ds }}',-1))
                                       )

                                      or effect_time_type=0
                                      )
                          )t )tt where rk=1

)

insert overwrite table jms_dm.dm_sqs_abnormal_network_interceptor_detail_dt
select
    t.province_id
     ,t.province
     ,t.network_code
     ,net.name as network_name
     ,t.abnormal_reason_code
     ,t.abnormal_reason
     ,t.effect_time_start
     ,t.effect_time_end
     --,t.create_time
     --,t.data_type
     ,if(t.cnt=1 and t.cnt_date='{{ execution_date | cst_ds }}',1,0) as is_incr --是否增 1新增
     ,if(t.cnt=1 and t.cnt_date=date_add('{{ execution_date | cst_ds }}',-1),1,0) as is_loss --是否流失 1新增
     ,t.cnt_date
     ,nvl(net.virt_code,net.agent_code) as virt_code
     ,nvl(net.virt_name,net.agent_name) as virt_name
     ,net.agent_name as belong_proxy
     ,net.agent_code as belong_proxy_code
     ,'{{ execution_date | cst_ds }}' as date_time
     ,effect_time_type
     ,net.city_id -- '城市ID'
     ,net.city_desc --'城市'
     ,net.zone_code as district_code  --片区code
     ,net.zone_name as district_desc  --片区名称
     ,net.area_code as area_code  --网管片区code
     ,net.area_name as area_name  --网管片区名称
     ,'{{ execution_date | cst_ds }}' as dt
     ,t.cnt_date as dy
from
    (
        select
            network_code
             ,network_name
             ,create_time
             ,data_type
             ,province
             ,province_id
             ,abnormal_reason_code
             ,abnormal_reason
             ,effect_time_start
             ,effect_time_end
             ,belong_proxy
             ,belong_proxy_code
             ,effect_time_type
             ,count(1) over(partition by network_code) as cnt
             ,cnt_date
        from details
    )t left join jms_dim.dim_network_whole_massage net on  net.code=t.network_code
    distribute by 1
;
----------------------------------------------------------------------------------------
insert overwrite table jms_dm.dm_sqs_abnormal_network_cnt_dt  partition(dt,dy)
select
    agent_code
     ,agent_name
     ,network_cnt
     ,nvl(befor_net_cnt,0)  as befor_net_cnt
     ,nvl(incr_cnt,0) as incr_network_cnt--新增会员
     ,cast((network_cnt-befor_net_cnt)/befor_net_cnt as decimal(16,4))*100 as network_cnt_rt
     ,nvl(loss_cnt,0) as low_network_cnt
     ,0 as target_value
     ,nvl(network_cnt-0,0) as over_value
     ,'筛单' as data_type
     ,'{{ execution_date | cst_ds }}' as date_time
     ,case when cnt_type=2 then '虚拟代理区'
           when cnt_type=1 then '代理区'
           when cnt_type=3 then '省份'
           when cnt_type=4 then '片区'
           when cnt_type=5 then '网管片区'
    end as agent_type
     ,virt_code --虚拟代理区code
     ,virt_name --虚拟代理区name
     ,'{{ execution_date | cst_ds }}' as dt
     ,'shaidan' as dy
from
    (
        select
             --cnt_date
            agent_code
             ,agent_name
             ,sum(if(cnt_date='{{ execution_date | cst_ds }}',network_cnt,0)) as network_cnt
             ,sum(if(cnt_date='{{ execution_date | cst_ds }}',incr_cnt,0)) as incr_cnt
             ,cnt_type
             ,sum(if(cnt_date=date_add('{{ execution_date | cst_ds }}',-1),loss_cnt,0)) as loss_cnt
             ,sum(if(cnt_date=date_add('{{ execution_date | cst_ds }}',-1),network_cnt,0))  as befor_net_cnt
             ,virt_code
             ,virt_name
        from
            (
                select
                    cnt_date
                     ,belong_proxy_code as agent_code
                     ,belong_proxy as agent_name
                     ,count(1) as  network_cnt
                     ,sum(is_incr) as incr_cnt
                     ,sum(is_loss) as loss_cnt
                     ,1 as cnt_type --代理区汇总
                     ,virt_code
                     ,virt_name
                from jms_dm.dm_sqs_abnormal_network_interceptor_detail_dt
                where dt = '{{ execution_date | cst_ds }}'
                group by cnt_date,belong_proxy_code,belong_proxy,virt_code,virt_name
                union all
                select
                    cnt_date
                     ,virt_code as agent_code
                     ,virt_name as agent_name
                     ,count(1) as  network_cnt
                     ,sum(is_incr) as incr_cnt
                     ,sum(is_loss) as loss_cnt
                     ,2 as cnt_type --虚拟代理区汇总
                     ,virt_code
                     ,virt_name
                from jms_dm.dm_sqs_abnormal_network_interceptor_detail_dt
                where dt = '{{ execution_date | cst_ds }}'
                group by cnt_date,virt_code,virt_name
                union all
                select
                    cnt_date
                     ,province_id as agent_code
                     ,province as agent_name
                     ,count(1) as  network_cnt
                     ,sum(is_incr) as incr_cnt
                     ,sum(is_loss) as loss_cnt
                     ,3 as cnt_type -- 省份汇总
                     ,virt_code
                     ,virt_name
                from jms_dm.dm_sqs_abnormal_network_interceptor_detail_dt
                where dt = '{{ execution_date | cst_ds }}'
                group by cnt_date,province_id,virt_code,province,virt_name
                union all
                select
                     cnt_date
                     ,nvl(district_code,'其他片区') as agent_code
                     ,district_desc as agent_name
                     ,count(1) as  network_cnt
                     ,sum(is_incr) as incr_cnt
                     ,sum(is_loss) as loss_cnt
                     ,4 as cnt_type -- 片区汇总
                     ,virt_code
                     ,virt_name as virt_name
                from jms_dm.dm_sqs_abnormal_network_interceptor_detail_dt
                where dt = '{{ execution_date | cst_ds }}'
                group by cnt_date,nvl(district_code,'其他片区'),virt_code,district_desc,virt_name
                union all
                select
                    cnt_date
                     ,area_code as agent_code
                     ,area_name as agent_name
                     ,count(1) as  network_cnt
                     ,sum(is_incr) as incr_cnt
                     ,sum(is_loss) as loss_cnt
                     ,5 as cnt_type -- 网管片区汇总
                     ,virt_code
                     ,virt_name as virt_name
                from jms_dm.dm_sqs_abnormal_network_interceptor_detail_dt
                where dt = '{{ execution_date | cst_ds }}'
                and area_code is not null
                group by cnt_date,area_code,area_name,virt_code,virt_name
            )t group by  agent_code,agent_name, cnt_type  ,virt_code ,virt_name
    )tt
    distribute by 1
;